SQLite Autoincrement

from SQLiteドキュメント

https://www.sqlite.org/autoinc.html

AUTOINCREMENTキーワードは余計なリソースを食うので、大抵の場合必要ない

SQLiteにおいては、INTEGER PRIMARY KEYがROWIDへのエイリアスであり、これらは常に64bit整数。

INSERT時、↑の値を明示的に指定しない場合、使用されていない整数すなわち大抵の場合現在使用されている最大のROWID+1の値で自動的に埋められる。これらはAUTOINCREMENTキーワードの設定に関わない。

もし、AUTOINCREMENTキーワードがINTEGER PRIMARY KEYのあとに現れる場合、自動的なROWIDの割当アルゴリズムを変更することになる。

2. Background

ROWIDの割当アルゴリズム

The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error. If no negative ROWID values are inserted explicitly, then automatically generated ROWID values will always be greater than zero.

このアルゴリズムにより、最大のROWIDが一度も使われず、かつテーブルにおける最大のROWIDをもつエントリが一度も削除されない限り、単調増加の一意なROWIDが生成される。

歯抜けしたROWIDは再利用される（重要）。

3. The AUTOINCREMENT Keyword

In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing.

歯抜けしたROWIDは再利用されない。

WITHOUT ROWIDの設定されたテーブル、または、INTEGER PRIMARY KEY以外のどのテーブルのカラムにおいても、AUTOINCREMENTは許可されない。

これらの性質が必要ない場合は、デフォルトにの挙動に任せておいたほうが良い

AUTOINCREMENTを利用すると、各行の挿入後に追加の処理が必要になり、挿入にかかる時間が増加する